# -*- coding: utf-8 -*-
import csv
import json
import os
from functools import reduce
from io import BytesIO
from operator import or_

import xlrd
from django.contrib.auth import authenticate, login
from django.contrib.auth.hashers import make_password
from django.core.paginator import Paginator, PageNotAnInteger, EmptyPage
from django.forms import model_to_dict
from django.http import JsonResponse, HttpResponse
from openpyxl.workbook import Workbook
from rest_framework import viewsets
from rest_framework.status import HTTP_400_BAD_REQUEST
from rest_framework.views import APIView

from itsdangerous import TimedJSONWebSignatureSerializer as Serilazer
from django.core.cache import cache
from conf.common_conf import res400, res201, res200, res204, name_re, phone_re
from conf.http_conf import REQUEST_GET_SUCCESS, REQUEST_PUT_SUCCESS, REQUEST_DELETE_SUCCESS, REQUEST_POST_SUCCESS, \
    FILE_FORMAT_ERROR, FILE_DOWNLOAD_SUCCESS, FILE_DOWNLOAD_FAILED, FILE_DOWNLOAD_EXIST, REQUEST_PUT_FAILED, NAME_ERROR, \
    REQUEST_POST_FAILED
from conf.user_conf import TIME_OUT, LOGIN_FAILED, NULL_ACCOUNT, USERNAME_IN_REVIEW, PHONE_ERROR, \
    USER_NAME_ERROR, REGISTER_SUCCESS
from course.models import TrainCourseTrainee, TrainCourse
from middleware.authentication import MyAuthentication
from tools.field_detection.name_detection import is_chinese_name
from tools.field_detection.phone_number_detection import is_valid_phone_number, validate_id_number
from tools.file_stream.download_file import download_file_to_excel
from tools.page_tools.page_info import paginate
from train import settings
from user.models import User, Teacher, Trainee
from user.serializers import UserSerializer, TeacherSerializer, TraineeSerializer
import pandas as pd
from rest_framework.response import Response
from rest_framework import status
import sqlite3
from django.db.models import Q
import openpyxl

from tools.db_conn.conn_info import db_insert_info, db_select_info, db_update_info
from django.db import connection, transaction
from tools.db_conn.conn_info import db_insert_info, db_select_info, db_update_info, db_select_info_params
from openpyxl.utils import get_column_letter
from openpyxl import load_workbook

serilazer = Serilazer(settings.SECRET_KEY, 3600 * 24 * 365)


class LoginView(APIView):

    def post(self, request):
        """
            :param: username-->name/phone_number.
            password.
            :return:login success or login failed.
            :annotation:customer ``Login`` interface.
            login using your ``phone number`` as your account with ``password``.
        """
        try:
            username, password = request.data.get('username'), request.data.get('password')
            res = is_valid_phone_number(username)
            if res is False:
                return JsonResponse(res400(PHONE_ERROR))
            try:
                user = User.objects.get(username=username)
            except:
                user = User.objects.get(phone_num=username)
            if user == None:
                return JsonResponse(res400(NULL_ACCOUNT))
            if user.is_active is False:
                return JsonResponse(res400(USERNAME_IN_REVIEW))
            user = authenticate(username=user.username, password=password)
            login(request, user)
            token = serilazer.dumps({"username": user.username})
            cache.set(token, user.username, TIME_OUT)
            res = {
                "code": 201,
                "token": str(token, encoding="utf-8"),
                "name": user.name,
                "id": user.id,
            }
            return JsonResponse(res)
        except Exception as e:
            print(str(e))
            return JsonResponse(res400(LOGIN_FAILED))


class RegisterView(APIView):

    def post(self, request):
        """
           :param: name.phone_number.password.
           :return:register success or register failed.
           :annotation:customer ``Register`` interface.
           register using your ``phone number`` and ``name`` and ``password``.
        """
        try:
            data = request.data
            username = data.get('username')
            name = data.get('name')
            res = is_valid_phone_number(username)
            if res is False:
                return JsonResponse(res400(PHONE_ERROR))
            flag = is_chinese_name(name)
            if flag is False:
                return JsonResponse(res400(USER_NAME_ERROR))
            user_objs = User.objects.filter(username=username)
            if len(user_objs) > 0:
                return JsonResponse(res400("PHONE_EXIT"))
            data["password"] = make_password(data.get('password'))
            data['is_active'] = False
            data['phone_num'] = username
            User.objects.create(**data)
            return JsonResponse(res201(REGISTER_SUCCESS))
        except Exception as e:
            print(str(e))
            return JsonResponse(res400(str(e)))

class UserView(viewsets.ModelViewSet):

    serializer_class = UserSerializer
    queryset = User.objects.all()
    authentication_classes = [MyAuthentication, ]

    def list(self, request, *args, **kwargs):
        """
            :param: pk or nothing.pk is user's id.
            :return:query success or query failed.
            :annotation:``Querying User Information`` interface.
            Query user information using the "user id" or all user information.
         """
        try:
            id,page,size = kwargs.get('pk',''),request.query_params.get('page', ''),request.query_params.get('size', '')
            if id != '':
                user_obj = User.objects.filter(id=id).order_by('-id')
                if user_obj.count() >0 :
                    return JsonResponse(res200('成功', model_to_dict(user_obj.first())))
                else:
                    return JsonResponse(res200('暂无用户'))
            if page == '' and size == '':
                results = User.objects.all().order_by('-id')
                return JsonResponse(res200(REQUEST_GET_SUCCESS, list(results)))

            res = User.objects.all().order_by('-id')
            paginator = Paginator(res, int(size))
            try:
                tar_page = paginator.page(page)
            except PageNotAnInteger:
                tar_page = paginator.page(1)
            except EmptyPage:
                tar_page = paginator.page(paginator.num_pages)
            final_data = UserSerializer(tar_page, many=True)
            return JsonResponse(res200(REQUEST_GET_SUCCESS, final_data.data, res.count()))
        except Exception as e:
            print(str(e))
            return JsonResponse(res400(str(e)))

    def update(self, request, *args, **kwargs):
        """
            :param: pk.name.phone_number.password.is_active.pk is user's id.
            :return:update success or update failed.
            :annotation:``Update User Information`` interface.
            Update user information using the "user id".
        """
        try:
            id = kwargs.get('pk')
            data = request.data
            data.pop('user_permissions', None)
            data.pop('groups', None)
            data.pop('password', None)
            username = request.data.get('username')
            res = is_valid_phone_number(username)
            if res is False:
                return JsonResponse(res400(PHONE_ERROR))
            name = request.data.get('name')
            flag = is_chinese_name(name)
            if flag is False:
                return JsonResponse(res400(USER_NAME_ERROR))
            User.objects.filter(id=id).update(**request.data)
            return JsonResponse(res201(REQUEST_PUT_SUCCESS))
        except Exception as e:
            print(str(e))
            return JsonResponse(res400(str(e)))

    def destroy(self, request, *args, **kwargs):
        """
        :param: pk.pk is user's id.
        :return:delete success or delete failed.
        :annotation:``Delete User Information`` interface.
        Delete user using the "user id".
        """
        instance = self.get_object()
        self.perform_destroy(instance)
        return JsonResponse(res204(REQUEST_DELETE_SUCCESS))


class TeacherView(viewsets.ModelViewSet):
    serializer_class = TeacherSerializer
    authentication_classes = [MyAuthentication, ]

    def create(self, request, *args, **kwargs):
        try:
            phone_num = request.data.get('phone_num')
            id_num=request.data.get('id_num')
            res = is_valid_phone_number(phone_num)
            is_num=validate_id_number(id_num)
            if res is False:
                return JsonResponse(res400("手机号格式不正确，请检查！"))
            if not phone_num:
                # 如果电话号码为空，返回错误
                return JsonResponse(res400("手机号为空！"))
                # 检查是否存在相同电话号码的教师
            if is_num is False:
                return JsonResponse(res400("身份证号格式不正确，请检查！"))
            objects_filter = Teacher.objects.filter(phone_num=phone_num)
            if objects_filter.exists():
                # 如果存在，你可以决定是返回错误还是更新教师信息
                # 这里假设你想更新教师信息
                teacher = objects_filter.first()
                for key, value in request.data.items():
                    if hasattr(teacher, key):
                        setattr(teacher, key, value)
                teacher.save()
                return JsonResponse(res200("更新成功!"))
            else:
                # 如果没有找到，创建新教师
                Teacher.objects.create(**request.data)
                return JsonResponse(res201( "创建成功!"))
        except Exception as e:
            print(str(e))
            return JsonResponse(res400(str(e)))
    def update(self, request, *args, **kwargs):
        try:
            phone_num = request.data.get('phone_num')
            objects_filter = Teacher.objects.filter(phone_num=phone_num)
            if objects_filter.count() > 0:
                objects_filter.update(**request.data)
                return JsonResponse(res201("更新成功！"))
            else:
                return JsonResponse(res201("创建成功！"))
        except Exception as e:
            print(str(e))
            return JsonResponse(res400(str(e)))
    def list(self, request, *args, **kwargs):
        """
            :param: pk or nothing.pk is user's id.
            :return:query success or query failed.
            :annotation:``Querying User Information`` interface.
            Query user information using the "user id" or all user information.
         """
        try:
            # 查询表中所有数据
            select_All_sql = ("select * from Teacher order by id desc")
            list_info = db_select_info(select_All_sql)

            # 获取分页参数，并转换为整数（如果可能）
            page_size_str = request.query_params.get('size', '10')
            page_size = int(page_size_str) if page_size_str.isdigit() else 10  # 默认为10

            page_number_str = request.query_params.get('page', '1')
            page_number = int(page_number_str) if page_number_str.isdigit() else 1  # 默认为1

            # 分页处理
            page_data, pagination_info = paginate(list_info, page_size, page_number)

            # 返回包含数据和分页信息的JSON响应
            # 注意：这里假设你有一个处理响应的函数 res201 和一个状态码/消息 REQUEST_GET_SUCCESS
            response_data = {
                'data': page_data,
                'pagination': pagination_info,
            }
            return JsonResponse(res200("查询成功！",response_data)) # 使用正确的状态码/消息
        except Exception as e:
            print(str(e))
            return JsonResponse(res400(str(e)))
class DownLoadView(viewsets.ModelViewSet):
    serializer_class = TeacherSerializer
    authentication_classes = [MyAuthentication, ]
    def list(self, request, *args, **kwargs):
        try:
            data = {
                "序号": [],
                "姓名": [],
                "性别": [],
                "是否内训师": [],
                "来源": [],
                "单位名称": [],
                "部门": [],
                "职务": [],
                "手机号": [],
                "课费(元)": [],
                "身份证号": [],
                "开户行信息": [],
                "个人介绍": [],
                "备注": []
            }
            frame = pd.DataFrame(data)
            # 假设文件已经生成在MEDIA_ROOT目录下
            file_path = 'model/teacher.xlsx'

            if os.path.exists(file_path):
                print("文件已存在，将被覆盖。")

                # 写入Excel文件
            frame.to_excel(file_path, index=False, engine='openpyxl')

            # 加载工作簿并调整列宽
            workbook = load_workbook(file_path)
            worksheet = workbook.active

            # 遍历第一行（表头）并根据内容设置列宽
            for col_idx, header in enumerate(frame.columns, 1):
                # 获取第一行中对应列的单元格
                cell = worksheet.cell(row=1, column=col_idx)

                # 如果单元格有值（表头通常都有值），则基于内容的长度设置列宽
                if cell.value is not None:
                    # 考虑到数字、日期等可能需要额外的空间，可以设置一个缩放因子
                    # 这里使用了一个简单的缩放因子1.1，你可以根据需要调整
                    max_length = len(str(cell.value)) * 1.1

                    # 列宽的最小值可以设置为一个合适的值，比如10（这相当于大约400像素的宽度）
                    adjusted_width = max(max_length, 10)

                    # 设置列宽（单位是字符宽度的1/256）
                    # 注意：openpyxl的列宽是以字符宽度的1/256为单位的，不是像素
                    worksheet.column_dimensions[get_column_letter(col_idx)].width = adjusted_width

                    # 保存工作簿
            workbook.save(file_path)

            return JsonResponse(res200("Excel文件已生成或已更新。",file_path))

        except PermissionError as e:
            # 处理权限错误
            return  JsonResponse(res400("无法写入文件：权限被拒绝,请检查下载文件是否已被打开。"))
        except Exception as e:
            # 处理其他异常
            return  JsonResponse(res400(str(e)))


class TeacherImportView(viewsets.ModelViewSet):
    serializer_class = TeacherSerializer
    authentication_classes = [MyAuthentication, ]

    def getexcel(self, request):
        try:
            workbook = openpyxl.load_workbook(request.data.get('file_path'))
        # 获取excel文件当前表格
            active = workbook.active
            info_count = []
            for row in active.iter_rows(min_row=2, min_col=2, max_col=14, max_row=active.max_row):
                row_ = [cell.value for cell in row]
                info_count.append(row_)
            insert_sql = ("insert into Teacher(name,gender,internal_trainer,source,unit_name,branch,post,"
                      "phone_num,cost,id_num,bank_info,self_intro,remark) values (?,?,?,?,?,?,?,?,?,?,?,?,?) ")
            select_sql = ("select distinct phone_num from Teacher")
            info = db_select_info(select_sql)
            info_list = []
            for row in info:
                info_list.append(int(list(row)[0]))
            update_info = []
            info_info = []
            for index,data in enumerate(info_count, start=1):
                phone_num = data[7]
                id_num = data[9]
                res = is_valid_phone_number(str(phone_num))
                is_num = validate_id_number(str(id_num))
                count = index + 1
                if phone_num in info_list:
                    update_tuple = tuple(data) + (phone_num,)
                    update_info.append(update_tuple)
                #插入的时候做校验
                else:
                    if res is False:
                        return JsonResponse(res400("第" + str(count) + "行手机号格式不正确，请检查！"))
                    if not phone_num:
                        # 如果电话号码为空，返回错误
                        return JsonResponse(res400("第" + str(count) + "行手机号为空，请检查！"))
                    if is_num is False:
                        return JsonResponse(res400("第" + str(count) + "行身份证号格式不正确，请检查！"))
                    info_info.append(tuple(data))
                update_sql = (
                    "update Teacher set name=?,gender=?,internal_trainer=?,source=?,unit_name=?,branch=?,post=?,phone_num=?,"
                    "cost=?,id_num=?,bank_info=?,self_intro=?,remark=? where phone_num=?")
            db_update_info(update_sql, update_info)
            db_insert_info(insert_sql, info_info)
            print("###更新数据:", update_info)
            print("###插入数据:", info_info)
            return  JsonResponse(res201(REQUEST_PUT_SUCCESS))
        except Exception as e:
            print(f"An error occurred: {e}")
            return JsonResponse(res400(str(e)))
#根据教师表中的姓名、来源、单位名称、部门、手机号进行模糊查询
class TeacherListView(viewsets.ModelViewSet):
    queryset = Teacher.objects.all()
    serializer_class = TeacherSerializer
    authentication_classes = [MyAuthentication, ]

    # Args:
    # request(HttpRequest): 请求对象。
    # search_field(str): 要搜索的字段名
    # search_term(str): 搜索词
    def list(self,request,*args, **kwargs):
        try:
            search_term = self.request.query_params.get('search_term')
            # search_term=eval(search_term)
            # 从查询参数中获取搜索词
            if search_term:
                select_sql=(f"select * from Teacher where name like '%{search_term}%' or source like '%{search_term}%' "
                            f"or unit_name like '%{search_term}%' or branch like '%{search_term}%' or post like '%{search_term}'or phone_num like '%{search_term}%'")
                print(select_sql)
                query_data = db_select_info(select_sql)
            # 分页参数
            page_size_str = request.query_params.get('size', '10')
            page_size = int(page_size_str) if page_size_str.isdigit() else 10  # 默认为10

            page_number_str = request.query_params.get('page', '1')
            page_number = int(page_number_str) if page_number_str.isdigit() else 1  # 默认为1

            # 分页处理
            page_data, pagination_info = paginate(query_data, page_size, page_number)

            # 返回包含数据和分页信息的JSON响应
            response_data = {
                'data': page_data,
                'pagination': pagination_info,
            }
            return  JsonResponse(res200(REQUEST_GET_SUCCESS, response_data))

        except Exception as e:
            print(str(e))
            return  JsonResponse(res400(str(e)))
class ExportTraineeView(viewsets.ModelViewSet):
    serializer_class = TeacherSerializer
    authentication_classes = [MyAuthentication, ]

    def export(self, request, *args, **kwargs):
        # 检查请求体是否为JSON
        if not request.content_type == 'application/json':
            return Response({"msg": "Invalid content type. Expected 'application/json'."}, status=400)
        try:
            # 解析JSON数据并确保它是一个列表
            if not isinstance(request.data, list):
                raise ValueError("Expected a list in the request body.")
            data_list = request.data
            print(data_list)
            # 构造Excel响应
            response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
            response['Content-Disposition'] = 'attachment; filename="D:\\learnproject\\djangoLearn\\training-management-system\\train\\train\\trainee_download.xlsx"'
            # 创建一个新的Excel工作簿
            wb = Workbook()
            ws = wb.active
            # 假设的表头
            fieldnames = ["序号", "姓名", "性别", "所在单位", "所属部门", "岗位",
                          "联系电话", "交建通账号", "课程", "备注"]
            # 写入表头
            ws.append(fieldnames)
            index = 1
            # 遍历数据并写入CSV
            for item in data_list:
                # 确保每个item都有我们需要的字段
                row_data = [
                    index,  # 序号
                    item.get('name', ''),
                    item.get('gender', ''),
                    item.get('unit_name', ''),
                    item.get('branch', ''),
                    item.get('post', ''),
                    item.get('phone_num', ''),
                    item.get('jcc_num', ''),
                    item.get('course_name', ''),
                    item.get('remark', ''),
                ]
                ws.append(row_data)
                index += 1  # 序号递增

            # 将工作簿保存为字节流
            output = BytesIO()
            # 将工作簿保存到字节流中
            wb.save(output)

            # 将字节流移动到响应体的开始位置
            output.seek(0)

            # 将字节流的内容写入到HttpResponse中
            response.encoding = "utf-8"
            response.write(output.read())

            return response
        except ValueError as e:
            # 处理数据类型错误
            return Response({"msg": str(e)}, status=400)
        except Exception as e:
            # 处理其他异常
            return Response({"msg": str(e)}, status=500)
class ExportView(viewsets.ModelViewSet):
    serializer_class = TeacherSerializer
    authentication_classes = [MyAuthentication, ]
    def export(self, request, *args, **kwargs):
        # 检查请求体是否为JSON
        if not request.content_type == 'application/json':
            return JsonResponse({"code":400,"msg": "Invalid content type. Expected 'application/json'."}, status=400)

        try:
            # 解析JSON数据并确保它是一个列表
            if not isinstance(request.data, list):
                raise ValueError("Expected a list in the request body.")

            data_list = request.data
            # 创建一个新的Excel工作簿
            wb = Workbook()
            ws = wb.active

            # 假设的表头
            fieldnames = ["序号", "姓名", "性别", "是否内训师", "来源", "单位名称", "部门", "职务", "手机号",
                          "课费(元)", "身份证号", "开户行信息", "个人介绍", "备注"]

            # 写入表头
            ws.append(fieldnames)
            index = 1
            # 遍历数据并写入Excel
            for item in data_list:
                row_data = [
                    index,  # 序号
                    item.get('name', ''),
                    item.get('gender', ''),
                    item.get('internal_trainer', ''),
                    item.get('source', ''),
                    item.get('unit_name', ''),
                    item.get('branch', ''),
                    item.get('post', ''),
                    item.get('phone_num', ''),
                    item.get('cost', ''),
                    item.get('id_num', ''),
                    item.get('bank_info', ''),
                    item.get('self_intro', ''),
                    item.get('remark', ''),
                ]
                ws.append(row_data)
                index += 1  # 序号递增
            # 定义文件保存路径（请根据实际情况修改路径）
            file_path = os.path.join('model/teacher_export.xlsx')
            # 确保文件保存目录存在
            os.makedirs(os.path.dirname(file_path), exist_ok=True)
            # 保存工作簿到文件
            wb.save(file_path)
            # 返回成功响应
            return JsonResponse(res200("Excel文件已经保存成功！",file_path))
        except Exception as e:
            # 捕获异常并返回错误信息
            return JsonResponse(res400(str(e)))


class TraineeView(viewsets.ModelViewSet):
    queryset = Trainee.objects.all()
    serializer_class = TraineeSerializer
    authentication_classes = [MyAuthentication, ]
    def create(self, request, *args, **kwargs):
        try:
            with transaction.atomic():
                if not request.FILES:
                    # 填报单表新增
                    name = request.data.get('name')
                    phone_num = request.data.get('phone_num')
                    gender = request.data.get('gender')
                    unit_name = request.data.get('unit_name')  # 注意：这里修正了拼写错误 'unit_nmae' -> 'unit_name'
                    branch = request.data.get('branch')
                    post = request.data.get('post')
                    jcc_num = request.data.get('jcc_num')
                    source = request.data.get('source')
                    # 检查所有字段是否非空
                    if not all([name, phone_num, gender, unit_name, branch, post, jcc_num, source]):
                        return JsonResponse(res400('请检查，有些字段为空'))
                    # 校验姓名
                    if not name_re(name):
                        return JsonResponse(res400("用户：" + name + "名字格式错误"))
                    # 校验手机号
                    if not phone_re(phone_num):
                        return JsonResponse(res400("用户：" + name + ";手机号：" + phone_num + "格式错误"))
                    traineeOne = Trainee.objects.filter(jcc_num=request.data.get('jcc_num'))
                    traineeTwo = Trainee.objects.filter(phone_num=request.data.get('phone_num'))
                    if traineeOne.count() > 0:
                        # 存在学员更新
                         traineeOne.update(**request.data)
                         trainCourseTraineeOne = TrainCourseTrainee.objects.filter(jcc_num=request.data.get('jcc_num'))
                         if trainCourseTraineeOne.count() > 0:
                            # 存在课程学员更新
                            trainCourseTraineeOne.update(**request.data)
                         return JsonResponse(res200(REQUEST_POST_SUCCESS))
                    elif traineeTwo.count() >0:
                        # 存在学员更新
                        traineeTwo.update(**request.data)
                        trainCourseTraineeTwo = TrainCourseTrainee.objects.filter(phone_num=request.data.get('phone_num'))
                        if trainCourseTraineeTwo.count() > 0:
                            # 存在课程学员更新
                            trainCourseTraineeTwo.update(**request.data)
                        return JsonResponse(res200(REQUEST_POST_SUCCESS))
                    else:
                        # 不存在学员创建
                        Trainee.objects.create(**request.data)
                        return JsonResponse(res200(REQUEST_POST_SUCCESS))

                # 通过导入批量新增
                f = request.FILES.get('fileOj')
                type_excel = f.name.split('.')[-1]
                if type_excel in ['xlsx', 'xls']:
                    # 开始解析上传的excel表格
                    wb = xlrd.open_workbook(filename=None, file_contents=f.read())
                    table = wb.sheets()[0]
                    nrows = table.nrows  # 行数
                    for i in range(1, nrows):  # 从0开始把表头省略则读取表头信息,如果从1开始则直接读取数据
                        rowValues = table.row_values(i)  # 一行的数据
                        item={'name':rowValues[1], 'gender':rowValues[2], 'unit_name':rowValues[3], 'branch':rowValues[4], 'post':rowValues[5],
                                    'phone_num':rowValues[6],'jcc_num':rowValues[7],'source':rowValues[8],'remark':rowValues[9]} # 这里可以进行其他的操作
                        # print(item)
                        name = item.get('name')
                        phone_num = item.get('phone_num')
                        gender = item.get('gender')
                        unit_name = item.get('unit_name')  # 注意：这里修正了拼写错误 'unit_nmae' -> 'unit_name'
                        branch = item.get('branch')
                        post = item.get('post')
                        jcc_num = item.get('jcc_num')
                        source = item.get('source')
                        # 检查所有字段是否非空
                        if not all([name, phone_num, gender, unit_name, branch, post, jcc_num, source]):
                            return JsonResponse(res400("用户："+name+' 有些字段为空'))
                        if not name_re(name):
                            return JsonResponse(res400("用户：" + name + "名字格式错误"))
                        if isinstance(phone_num, str):
                            return JsonResponse(res400("用户：" + name + ";手机号：" + phone_num + "格式有错误"))
                        if not phone_re(str(int(phone_num))):
                            return JsonResponse(res400("用户："+name+";手机号："+str(int(phone_num))+"格式有错误"))
                        item['phone_num'] = int(phone_num)
                        if not isinstance(jcc_num, str):
                            item['jcc_num'] = int(item.get('jcc_num'))
                        # item['jcc_num'] = int(item.get('jcc_num'))
                        print(item)
                        traineeOne = Trainee.objects.filter(jcc_num=item.get('jcc_num'))
                        traineeTwo = Trainee.objects.filter(phone_num=item.get('phone_num'))
                        if traineeOne.count() > 0:
                            # 存在学员更新
                            traineeOne.update(**item)
                            trainCourseTraineeOne = TrainCourseTrainee.objects.filter(jcc_num=item.get('jcc_num'))
                            if trainCourseTraineeOne.count() > 0:
                                # 存在课程学员更新
                                trainCourseTraineeOne.update(**item)
                        elif traineeTwo.count() > 0:
                            # 存在学员更新
                            traineeTwo.update(**item)
                            trainCourseTraineeTwo = TrainCourseTrainee.objects.filter(phone_num=item.get('phone_num'))
                            if trainCourseTraineeTwo.count() > 0:
                                # 存在课程学员更新
                                trainCourseTraineeTwo.update(**item)
                        else:
                            # 不存在学员创建
                            Trainee.objects.create(**item)
                    return JsonResponse(res200(REQUEST_POST_SUCCESS))
                return JsonResponse(res400(REQUEST_POST_FAILED))
        except Exception as e:
            return JsonResponse(res400(str(e)))
    def destroy(self, request, *args, **kwargs):
        """
        :param: pk.pk is user's id.
        :return:delete success or delete failed.
        :annotation:``Delete User Information`` interface.
        Delete user using the "user id".
        """
        ids = request.data.get('ids', [])  # 从 JSON 请求体中获取 phone_number 列表
        if not ids or not isinstance(ids, list):
            return Response({"msg": "没有提供有效的学员列表"}, status=status.HTTP_400_BAD_REQUEST)

        # 使用 Django ORM 的 filter 和 delete 方法来批量删除用户
        traineeList = Trainee.objects.filter(id__in=ids)
        # 注意：这里使用 __in 来匹配列表中的多个电话号码
        trainCourseTrainee = TrainCourseTrainee.objects.filter(id__in=ids).values("name").distinct()
        trainCourseTraineeList = []
        for item in trainCourseTrainee:
            trainCourseTraineeList.append(item.get('name'))
        trainCourseTraineeStr=','.join(trainCourseTraineeList)
        if trainCourseTraineeList :
            return Response({"message": f"学员：[{trainCourseTraineeStr}]正在培训中无法删除"}, status=status.HTTP_400_BAD_REQUEST)

        trainee = Trainee.objects.filter(id__in=ids)
        traineeList = []
        for item in trainee.values("name"):
            traineeList.append(item.get('name'))
        traineeStr = ','.join(traineeList)
        # 返回响应
        if traineeList:
            trainee.delete()
            return Response({"message": f"成功删除了学员：[{traineeStr}]"}, status=status.HTTP_204_NO_CONTENT)
        return JsonResponse(res204(REQUEST_DELETE_SUCCESS))
    def update(self, request, *args, **kwargs):
        try:
            id = request.data.get('id')

            trainee = Trainee.objects.filter(id=id)
            if trainee.count() > 0:
                # 修改学员
                trainee.update(**request.data)
                print(id)
                trainCourseTrainee = TrainCourseTrainee.objects.filter(train_course_id = id)
                if trainCourseTrainee.count() > 0:
                    trainCourseTrainee.update(**request.data)
                return JsonResponse(res204(REQUEST_PUT_SUCCESS))
            else:
                return JsonResponse(res204(REQUEST_PUT_FAILED))
        except Exception as e:
            print(str(e))
            return JsonResponse(res400(str(e)))

    # 根据学员中的姓名、来源、单位名称、部门、手机号进行模糊查询
    def list(self, request, *args, **kwargs):
        try:
            # queryset = super().get_queryset()  # 获取默认的查询集
            search_term = self.request.query_params.get('search_term','')
            # 从查询参数中获取搜索词
            size,page = request.query_params.get('size',10),request.query_params.get('page',1)
            query = (
                    Q(name__icontains=search_term) |
                    Q(source__icontains=search_term) |
                    Q(unit_name__icontains=search_term) |
                    Q(branch__icontains=search_term) |
                    Q(phone_num__icontains=search_term)
            )
            # 执行查询
            results = Trainee.objects.filter(query).order_by('-id')

            # 初始化分页器
            paginator = Paginator(results, size)
            try:
                # 获取指定页码的结果
                page_results = paginator.page(page)
            except PageNotAnInteger:
                # 如果页码不是一个整数，则返回第一页
                page_results = paginator.page(1)
            except EmptyPage:
                # 如果页码超出范围，则返回最后一页
                page_results = paginator.page(paginator.num_pages)
            final_data = TraineeSerializer(page_results, many=True)
            return JsonResponse(res200('成功', final_data.data, results.count()))
            #     select_sql = (
            #         f"SELECT t.id,t.name,t.gender,t.branch,t.post,t.phone_num,t.jcc_num,t.remark,t.unit_name,t.source,a.course_name "
            #         f"FROM trainee t LEFT JOIN (SELECT t.name, t.jcc_num,GROUP_CONCAT(tc.course_name) as course_name from train_course_trainee t LEFT joIN train_course tc ON tc.id = t.train_course_id GROUP BY t.name, t.jcc_num) a on a.jcc_num = t.jcc_num"
            #         f" where t.name  like '%{search_term}%' or t.source like '%{search_term}%' or t.unit_name like '%{search_term}%' or t.branch like '%{search_term}%' or t.post like '%{search_term}'or t.phone_num like '%{search_term}%'")
            #
            #     # select_sql=(f"select * from Teacher where name like '%{search_term}%' or source like '%{search_term}%' "
            #     #             f"or unit_name like '%{search_term}%' or branch like '%{search_term}%' or post like '%{search_term}'or phone_num like '%{search_term}%'")
            #     # print(select_sql)
            #     query_data = db_select_info(select_sql)
            # else:
            #     select_sql = (
            #          f"SELECT t.id, t.name,t.gender,t.branch,t.post,t.phone_num,t.jcc_num,t.remark,t.unit_name,t.source,a.course_name "
            #         f"FROM trainee t LEFT JOIN (SELECT t.name, t.jcc_num,GROUP_CONCAT(tc.course_name) as course_name from train_course_trainee t LEFT joIN train_course tc ON tc.id = t.train_course_id GROUP BY t.name, t.jcc_num) a on a.jcc_num = t.jcc_num")
            #     # print(select_sql)
            #     query_data = db_select_info(select_sql)

            # # 分页参数
            # page_size_str = request.query_params.get('size', '10')
            # page_size = int(page_size_str) if page_size_str.isdigit() else 10  # 默认为10
            #
            # page_number_str = request.query_params.get('page', '1')
            # page_number = int(page_number_str) if page_number_str.isdigit() else 1  # 默认为1
            #
            # # 分页处理
            # page_data, pagination_info = paginate(query_data, page_size, page_number)
            #
            # # 返回包含数据和分页信息的JSON响应
            # response_data = {
            #     'data': page_data,
            #     'pagination': pagination_info,
            # }
            # return Response(res200(REQUEST_GET_SUCCESS, response_data))
        except Exception as e:
            print(str(e))
            return Response(res400(str(e)))


# 下载学员模板
class DownLoadTraineeView(viewsets.ModelViewSet):
    serializer_class = TraineeSerializer
    authentication_classes = [MyAuthentication, ]
    def list(self, request, *args, **kwargs):
        file_path =  "model/trainee.xlsx"
        if os.path.exists(file_path):
            print("文件已存在，将被覆盖。")  # 使用正确的状态码/消息

        try:
            data = {
                "序号":[],
                "姓名": [],
                "性别": [],
                "所在单位": [],
                "所属部门": [],
                "岗位": [],
                "联系电话": [],
                "交建通账号": [],
                "来源": [],
                "备注": []

            }
            frame = pd.DataFrame(data)

            # 写入Excel文件
            frame.to_excel(file_path, index=False, engine='openpyxl')

            # 加载工作簿并调整列宽
            workbook = load_workbook(file_path)
            worksheet = workbook.active

            # 遍历第一行（表头）并根据内容设置列宽
            for col_idx, header in enumerate(frame.columns, 1):
                # 获取第一行中对应列的单元格
                cell = worksheet.cell(row=1, column=col_idx)

                # 如果单元格有值（表头通常都有值），则基于内容的长度设置列宽
                if cell.value is not None:
                    # 考虑到数字、日期等可能需要额外的空间，可以设置一个缩放因子
                    # 这里使用了一个简单的缩放因子1.1，你可以根据需要调整
                    max_length = len(str(cell.value)) * 1.1

                    # 列宽的最小值可以设置为一个合适的值，比如10（这相当于大约400像素的宽度）
                    adjusted_width = max(max_length, 10)

                    # 设置列宽（单位是字符宽度的1/256）
                    # 注意：openpyxl的列宽是以字符宽度的1/256为单位的，不是像素
                    worksheet.column_dimensions[get_column_letter(col_idx)].width = adjusted_width
                    # 保存工作簿
            workbook.save(file_path)
            return JsonResponse(res200("Excel文件已生成或已更新。",file_path))
        except Exception as e:
            print(str(e))
            return JsonResponse(res400(str(e)))


class DeleteView(viewsets.ModelViewSet):
    serializer_class = TeacherSerializer
    authentication_classes = [MyAuthentication, ]
    def destroy(self, request, *args, **kwargs):
        """
        :param: pk.pk is user's id.
        :return:delete success or delete failed.
        :annotation:``Delete User Information`` interface.
        Delete user using the "user id".
        """
        phone_numbers = request.data.get('phone_number', [])  # 从 JSON 请求体中获取 phone_number 列表
        if not phone_numbers or not isinstance(phone_numbers, list):
            return Response({"msg": "没有提供有效的电话号码列表"}, status=status.HTTP_400_BAD_REQUEST)

            # 使用 Django ORM 的 filter 和 delete 方法来批量删除用户
        # 注意：这里使用 __in 来匹配列表中的多个电话号码
        deleted_count, _ = Teacher.objects.filter(phone_num__in=phone_numbers).delete()

        # 返回响应
        return JsonResponse(res200("成功删除!"))



